﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using NLBS.Entity;
using System.Data;

namespace NLBS.Data
{
    public partial class AccessDataProvider : DbProvider
    {
        #region misc

        public override void UpdateGlobalCounter()
        {
            DbCommand dbCommand=db.GetSqlStringCommand("select count(log_id) from blog_Article");
            
            int artCount=Convert.ToInt32(db.ExecuteScalar(dbCommand));
            dbCommand=db.GetSqlStringCommand(string.Format(@"UPDATE blog_Settings SET set_value0 ={0}  
            WHERE set_name='counterArticle'",artCount));
            db.ExecuteNonQuery(dbCommand);

            dbCommand=db.GetSqlStringCommand("select count(comm_id) from blog_Comment");
            int commentCount=Convert.ToInt32(db.ExecuteScalar(dbCommand));
            dbCommand=db.GetSqlStringCommand(string.Format(@"UPDATE blog_Settings SET set_value0 ={0} 
            WHERE set_name='counterComment'",commentCount));
            db.ExecuteNonQuery(dbCommand);

            dbCommand=db.GetSqlStringCommand("select count(user_id) from blog_User");
            int userCount=Convert.ToInt32(db.ExecuteScalar(dbCommand));
            dbCommand=db.GetSqlStringCommand(string.Format(@"UPDATE blog_Settings SET set_value0 ={0} 
            WHERE set_name='counterUser'",userCount));
            db.ExecuteNonQuery(dbCommand);
        }

        public override void UpdateCategoryCounter()
        {
            IList<Category> catList = this.GetCategory();
            foreach (Category item in catList)
            {
                DbCommand dbCommand = db.GetSqlStringCommand(string.Format("select count(log_id) from blog_article where log_catID={0}", item.CategoryID));
                int countArticle = (int)db.ExecuteScalar(dbCommand);
                DbCommand updateCommand = db.GetSqlStringCommand(string.Format("update blog_category set cat_articleCount={0} where cat_id={1}", countArticle, item.CategoryID));
                db.ExecuteNonQuery(updateCommand);
            }
        }

        public override void UpdateArticleCounter()
        {
            DbCommand articleCommand = db.GetSqlStringCommand("select log_id from blog_article");
            DataSet ds = db.ExecuteDataSet(articleCommand);
            DataRowCollection articleData = ds.Tables[0].Rows;
            foreach (DataRow item in articleData)
            {
                DbCommand dbCommand = db.GetSqlStringCommand(string.Format("select count(comm_id) from blog_Comment where log_id={0}", (int)item["log_id"]));
                int countComment = (int)db.ExecuteScalar(dbCommand);
                DbCommand updateCommand = db.GetSqlStringCommand(string.Format("update blog_article set log_commentCount={0} where log_id={1}", countComment, (int)item["log_id"]));
                db.ExecuteNonQuery(updateCommand);
            }

        }

        public override void UpdateUserCounter()
        {
            DbCommand userCommand = db.GetSqlStringCommand("select user_id from blog_user");
            DataSet ds = db.ExecuteDataSet(userCommand);
            DataRowCollection userData = ds.Tables[0].Rows;
            foreach (DataRow item in userData)
            {
                DbCommand commentCommand = db.GetSqlStringCommand(string.Format("select count(comm_id) from blog_Comment where comm_authorid={0}", (int)item["user_id"]));
                int countComment = (int)db.ExecuteScalar(commentCommand);

                DbCommand articleCommand = db.GetSqlStringCommand(string.Format("select count(log_id) from blog_Article where log_authorid={0}", (int)item["user_id"]));
                int countArticle = (int)db.ExecuteScalar(articleCommand);
                DbCommand updateCommand = db.GetSqlStringCommand(string.Format("update blog_user set user_commentCount={0},user_articleCount={1} where user_id={2}", countComment, countArticle, (int)item["user_id"]));
                db.ExecuteNonQuery(updateCommand);
            }
        }

        public override void CleanInactiveUserAccount()
        {
            DateTime now = DateTime.Now.AddDays(-30);
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format("DELETE FROM [blog_User] WHERE user_commentCount=0 AND user_articleCount=0 AND user_lastVisit<'{0}'", now.ToString()));
            db.ExecuteNonQuery(dbCommand);
            DbCommand userCountCommand = db.GetSqlStringCommand("SELECT count(user_id) as cnt FROM [blog_User]");
            int countUser = (int)db.ExecuteScalar(userCountCommand);
            DbCommand settingCommand = db.GetSqlStringCommand(string.Format("UPDATE blog_settings set set_value0={0} where set_name='counterUser'", countUser));
            db.ExecuteNonQuery(settingCommand);
        }

        public override void CleanVisitorCounter()
        {
            DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM [blog_VisitorRecord]");
            db.ExecuteNonQuery(dbCommand);
        }

        public override void CleanGuestBookEntries()
        {
            DateTime now = DateTime.Now.AddDays(-30);
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format("DELETE FROM [Guestbook] WHERE gb_postTime<'{0}'", now.ToString()));
            db.ExecuteNonQuery(dbCommand);
        }

        #endregion
    }
}
